package com.studentmanagement.dao;

import com.studentmanagement.model.Enrollment;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface EnrollmentMapper {
    
    @Select("SELECT e.*, s.name as student_name, s.student_id as student_number, " +
            "c.name as course_name, c.course_code as course_code " +
            "FROM enrollment e " +
            "JOIN student s ON e.student_id = s.id " +
            "JOIN course c ON e.course_id = c.id " +
            "ORDER BY e.enrollment_date DESC")
    List<Enrollment> getAllEnrollments();
    
    @Select("SELECT e.*, s.name as student_name, s.student_id as student_number, " +
            "c.name as course_name, c.course_code as course_code " +
            "FROM enrollment e " +
            "JOIN student s ON e.student_id = s.id " +
            "JOIN course c ON e.course_id = c.id " +
            "WHERE e.id = #{id}")
    Enrollment getEnrollmentById(@Param("id") Integer id);
    
    @Select("SELECT e.*, s.name as student_name, s.student_id as student_number, " +
            "c.name as course_name, c.course_code as course_code " +
            "FROM enrollment e " +
            "JOIN student s ON e.student_id = s.id " +
            "JOIN course c ON e.course_id = c.id " +
            "WHERE e.student_id = #{studentId} " +
            "ORDER BY e.enrollment_date DESC")
    List<Enrollment> getEnrollmentsByStudentId(@Param("studentId") Integer studentId);
    
    @Select("SELECT e.*, s.name as student_name, s.student_id as student_number, " +
            "c.name as course_name, c.course_code as course_code " +
            "FROM enrollment e " +
            "JOIN student s ON e.student_id = s.id " +
            "JOIN course c ON e.course_id = c.id " +
            "WHERE e.course_id = #{courseId} " +
            "ORDER BY s.name")
    List<Enrollment> getEnrollmentsByCourseId(@Param("courseId") Integer courseId);
    
    @Select("SELECT e.*, s.name as student_name, s.student_id as student_number, " +
            "c.name as course_name, c.course_code as course_code " +
            "FROM enrollment e " +
            "JOIN student s ON e.student_id = s.id " +
            "JOIN course c ON e.course_id = c.id " +
            "WHERE e.status = #{status} " +
            "ORDER BY e.enrollment_date DESC")
    List<Enrollment> getEnrollmentsByStatus(@Param("status") String status);
    
    @Select("SELECT e.*, s.name as student_name, s.student_id as student_number, " +
            "c.name as course_name, c.course_code as course_code " +
            "FROM enrollment e " +
            "JOIN student s ON e.student_id = s.id " +
            "JOIN course c ON e.course_id = c.id " +
            "WHERE s.name LIKE #{keyword} OR s.student_id LIKE #{keyword} " +
            "OR c.name LIKE #{keyword} OR c.course_code LIKE #{keyword} " +
            "ORDER BY e.enrollment_date DESC")
    List<Enrollment> searchEnrollments(@Param("keyword") String keyword);
    
    @Insert("INSERT INTO enrollment (student_id, course_id, enrollment_date, grade, status) " +
            "VALUES (#{studentId}, #{courseId}, #{enrollmentDate}, #{grade}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertEnrollment(Enrollment enrollment);
    
    @Update("UPDATE enrollment SET student_id = #{studentId}, course_id = #{courseId}, " +
            "enrollment_date = #{enrollmentDate}, grade = #{grade}, status = #{status} " +
            "WHERE id = #{id}")
    int updateEnrollment(Enrollment enrollment);
    
    @Delete("DELETE FROM enrollment WHERE id = #{id}")
    int deleteEnrollment(@Param("id") Integer id);
    
    @Select("SELECT COUNT(*) FROM enrollment WHERE student_id = #{studentId} AND course_id = #{courseId}")
    int checkDuplicateEnrollment(@Param("studentId") Integer studentId, @Param("courseId") Integer courseId);
    
    @Select("SELECT " +
            "COUNT(*) as total_courses, " +
            "SUM(c.credits) as total_credits, " +
            "AVG(e.grade) as average_grade " +
            "FROM enrollment e " +
            "JOIN course c ON e.course_id = c.id " +
            "WHERE e.student_id = #{studentId} AND e.status = 'COMPLETED' AND e.grade IS NOT NULL")
    Enrollment getStudentGradeStats(@Param("studentId") Integer studentId);
    
    @Select("SELECT COUNT(*) FROM enrollment WHERE course_id = #{courseId} AND status = 'ACTIVE'")
    int getEnrolledStudentCount(@Param("courseId") Integer courseId);
    
    @Select("SELECT COUNT(*) FROM enrollment WHERE student_id = #{studentId} AND course_id = #{courseId} AND status = 'ACTIVE'")
    int isEnrolled(@Param("studentId") Integer studentId, @Param("courseId") Integer courseId);
} 